package com.plug.service;

import com.plug.common.date.DateConvert;
import com.plug.common.jdbc.DBConnectionManager;
import com.plug.common.jdbc.DBConnectionPool;
import com.plug.common.jdbc.JDBCInfoBean;
import com.plug.entity.PlugContrast;
import com.plug.entity.PlugDatasource;
import com.plug.entity.PlugOperationRecord;
import com.plug.entity.PlugRecord;
import com.plug.repository.PlugDatasourceMapper;
import com.plug.repository.PlugOperationRecordMapper;
import com.plug.repository.PlugRecordMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.sql.*;
import java.util.*;
import java.util.Date;

/**
 * Created by admin on 2016/12/19.
 */
@Service("plugRecordService")
public class PlugRecordServiceImpl implements IPlugRecordService{


    private Logger logger = LoggerFactory.getLogger(getClass());

    @Resource
    private PlugRecordMapper plugRecordMapper;
    @Resource
    private PlugDatasourceMapper plugDatasourceMapper;

    @Resource
    private PlugOperationRecordMapper plugOperationRecordMapper;

    private DBConnectionManager dbConnectionManager = DBConnectionManager.getInstance();

    /**
     * 保存操作人员记录及日志
     * @param plugRecordList
     * @param plugOperationRecord
     * @return
     */
    public int saveRecordAndOperation(List<PlugRecord> plugRecordList, PlugOperationRecord plugOperationRecord) throws Exception {
        PlugOperationRecord p1 = plugOperationRecordMapper.selectByPrimaryKey(plugOperationRecord.getId());
        int result = 0;
        if(p1 != null) {
            result = plugOperationRecordMapper.updateByPrimaryKeySelective(plugOperationRecord);
        } else {
            result = plugOperationRecordMapper.insertSelective(plugOperationRecord);
        }
        if(result > 0)  {
            try {
                return plugRecordMapper.insertBatch(plugRecordList);
            }catch (DataAccessException e){
                throw new Exception("插入数据量过大，请使用即时任务对比！");
            }
        } else {
            return -1;
        }
    }

    /**
     * 获取数据库对比表列名
     * @param plugDatasource 数据库名称
     * @param tableName 数据表名
     * @return
     */
    public List<String> findHistoryColumn(PlugDatasource plugDatasource, String tableName,String column) throws SQLException {
        List<String> resultList = new ArrayList<>();
        Connection connection = dbConnectionManager.getConnection(DBConnectionPool.createJDBCInfoBean(plugDatasource));
        PreparedStatement ps = null;
        ResultSet rs = null;
       try{
           ps = connection.prepareStatement("select "+ column + " from "+ tableName);
           rs = ps.executeQuery();
           ResultSetMetaData metaData = rs.getMetaData(); // 取得结果集的元元素
           int colCount = metaData.getColumnCount(); // 取得所有列的个数
           for (int i = 1; i <= colCount; i++) { // 对于该记录的每一列
               resultList.add(metaData.getColumnName(i));
           }
           resultList.add("split_column");
           for (int i = 1; i <= colCount; i++) { // 对于该记录的每一列
               resultList.add("tgt_" + metaData.getColumnName(i));
           }
           return resultList;
       } catch (SQLException e){
           throw new SQLException("数据库连接/查询异常！");
       } finally {
           try {
               if(rs != null) {
                   rs.close();
               }
               if(ps != null) {
                   ps.close();
               }
           } catch (SQLException e) {
               e.printStackTrace();
           }
           dbConnectionManager.freeConnection(plugDatasource.getDsName(), connection);
       }
    }

    public List<Map<String, Object>> findByOperationAll(int offset,int limit) {
        return plugRecordMapper.findByOperationAll(offset, limit);
    }

    public List<PlugRecord> findAll(int offset, int limit) {
        return plugRecordMapper.findAll(offset, limit);
    }

    /**
     * 根据数据源、表名查询表结构
     * @param plugDatasource
     * @param tableName
     * @return
     */
    public Map<String, Object> findTableStrut(PlugDatasource plugDatasource, String tableName) throws SQLException {
        Connection conn = dbConnectionManager.getConnection(DBConnectionPool.createJDBCInfoBean(plugDatasource));
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Map<String, Object> map = new HashMap<>();
            ps = conn.prepareStatement("select COLUMN_NAME,DATA_TYPE from information_schema.columns " +
                    "where table_schema = '" + plugDatasource.getDsDesc() + "' and table_name = '" + tableName + "' and " +
                    "DATA_TYPE in ('date','time','datetime','timestamp','bigint','varchar')");
            logger.debug("sql:{}","select COLUMN_NAME,DATA_TYPE from information_schema.columns " +
                    "where table_schema = '" + plugDatasource.getDsDesc() + "' and table_name = '" + tableName + "' and " +
                    "DATA_TYPE in ('date','time','datetime','timestamp','bigint','varchar')");
            rs = ps.executeQuery();
            while (rs.next()) {
                map.put(rs.getString("COLUMN_NAME"), rs.getString("DATA_TYPE"));
            }
            return map;
        } catch (SQLException e){
            throw new SQLException("数据库连接/查询异常！");
        } finally {
            try {
                if(rs != null){
                    rs.close();
                }
                if(ps != null) {
                    ps.close();
                }
            } catch (SQLException e) {
                throw new SQLException("数据库连接关闭异常！");
            }
            dbConnectionManager.freeConnection(plugDatasource.getDsName(),conn);
        }
    }

    @Override
    public PlugOperationRecord findByID(String id) {
        return plugOperationRecordMapper.selectByPrimaryKey(id);
    }

    public int findTotal() {
        return plugRecordMapper.findTotal();
    }

    public List<String> findTableColumn(PlugDatasource plugDatasource, String tableName) throws SQLException {
        List<String> list = new ArrayList<>();
        Connection connection = dbConnectionManager.getConnection(DBConnectionPool.createJDBCInfoBean(plugDatasource));
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            ps = connection.prepareStatement("select * from " + tableName);
            rs = ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int colCount = metaData.getColumnCount();
            for (int i = 1; i <= colCount; i++) {
                list.add(metaData.getColumnName(i));
            }
            return list;
        } catch (SQLException e){
            throw new SQLException("数据库连接/查询异常！");
        } finally {
            try {
                if(rs != null){
                    rs.close();
                }
                if(ps != null) {
                    ps.close();
                }
            } catch (SQLException e){
                e.printStackTrace();
            }
            dbConnectionManager.freeConnection(plugDatasource.getDsName(), connection);
        }
    }

    /**
     * 根据记录ID查询出所有对比记录
     * @param crId
     * @return
     */
    public List<PlugRecord> findByIDAll(String crId) {
        return plugRecordMapper.findByIDAll(crId);
    }

    /**
     * 根据条件查询起始ID
     * @param plugDatasource 数据源对象
     * @param tableName 表名
     * @param offset 开始位置
     * @param limit 结束位置
     * @param column 查询列名
     * @param sort 排序方式
     * @param plugContrast 任务配置对象
     * @return
     */
    public Long[] getMaxAndMinIndex(PlugDatasource plugDatasource, String tableName, Integer offset, Integer limit, String column, String sort, PlugContrast plugContrast) throws Exception {
        Long[] result = new Long[2];
        Connection connection = dbConnectionManager.getConnection(DBConnectionPool.createJDBCInfoBean(plugDatasource));
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            String sql = "";
            if(offset == null || limit == null){
                ps = connection.prepareStatement("select " + plugContrast.getPrimaryKey() + " from " + tableName + " order by "+ plugContrast.getPrimaryKey() + " desc");
                rs = ps.executeQuery();
                Long maxIndex = 0L;
                Long minIndex = 0L;
                while (rs.next()){
                    maxIndex = rs.getLong(plugContrast.getPrimaryKey());
                    break;
                }
                result[0] = minIndex;
                result[1] = maxIndex;
            } else {
                ps = connection.prepareStatement("select " + column + " from " + tableName + " order by "+ plugContrast.getPrimaryKey() + " "  + sort + " limit " + offset + "," + limit);
                rs = ps.executeQuery();
                Long maxIndex = 0L;
                Long minIndex = 0L;
                if(rs.first()){
                    if(sort.equals("desc")){
                        maxIndex = Long.valueOf(rs.getString(plugContrast.getPrimaryKey()));
                    } else {
                        minIndex = Long.valueOf(rs.getString(plugContrast.getPrimaryKey()));
                    }
                }
                if(rs.last()){
                    if(sort.equals("desc")){
                        minIndex = Long.valueOf(rs.getString(plugContrast.getPrimaryKey()));
                    } else {
                        maxIndex = Long.valueOf(rs.getString(plugContrast.getPrimaryKey()));
                    }
                }
                result[0] = minIndex;
                result[1] = maxIndex;
            }
        }catch (Exception e){
            throw new Exception("查询对比数据异常，请检查对比表数据条件数据是否正确！");
        }finally {
            try{
                if(rs != null)
                    rs.close();
                if(ps != null)
                    ps.close();
            } catch (SQLException e){
                e.printStackTrace();
            }
            dbConnectionManager.freeConnection(plugDatasource.getDsName(), connection);
        }
        return result;
    }
    /**
     根据数据库别名、数据表名数据
     * @param plugDatasource 数据库名称
     * @param tableName 数据表名
     * @param column 查询列名称
     * @param sort 排序方式
     * @return
     */
    public Map<String,Map<String, Object>> findHistoryRecord(PlugDatasource plugDatasource, String tableName,Long[] index, String column,String sort,PlugContrast plugContrast) throws Exception {
        Map<String,Map<String, Object>> reslutMap = new HashMap<>();
        Connection connection = dbConnectionManager.getConnection(DBConnectionPool.createJDBCInfoBean(plugDatasource));
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement("select " + column + " from " + tableName + " where "+plugContrast.getPrimaryKey()+" >= "
                    +index[0]+" and "+plugContrast.getPrimaryKey()+" <= "+index[1]+" order by "+ plugContrast.getPrimaryKey());
            logger.debug("----------------find sql -------{},{},{}",index[0],index[1],"select " + column + " from " + tableName + " where "+plugContrast.getPrimaryKey()+" >= "
                    +index[0]+" and "+plugContrast.getPrimaryKey()+" <= "+index[1]+" order by "+ plugContrast.getPrimaryKey());
            rs = ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData(); // 取得结果集的元元素
            int colCount = metaData.getColumnCount(); // 取得所有列的个数
            while (rs.next()){
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 1; i <= colCount; i++) {
                    Object value = rs.getObject(i);
                    map.put(metaData.getColumnName(i), value);
                }
                reslutMap.put(rs.getString(plugContrast.getPrimaryKey()), map);
            }
        } catch (Exception e){
            throw new Exception("查询对比数据异常，请检查对比表数据条件数据是否正确！");
        } finally {
            try{
                if(rs != null)
                rs.close();
                if(ps != null)
                ps.close();
            } catch (SQLException e){
                e.printStackTrace();
            }
            dbConnectionManager.freeConnection(plugDatasource.getDsName(), connection);
        }
        return reslutMap;
    }

    /**
     * 多线程分批次对比
     * @param plugDatasource 数据库名称
     * @param tableName 数据表名
     * @param index 起始ID
     * @param column 查询列名称
     * @param plugContrast 任务配置对象
     * @param offset 分页开始位
     * @param limit 分页结束位
     * @return
     * @throws Exception
     */
    public Map<String, Map<String, Object>> findHistoryRecord(PlugDatasource plugDatasource, String tableName, Long[] index, String column, PlugContrast plugContrast) throws Exception {
        Map<String,Map<String, Object>> reslutMap = new HashMap<>();
        Connection connection = dbConnectionManager.getConnection(DBConnectionPool.createJDBCInfoBean(plugDatasource));
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            String sql = "select " + column + " from " + tableName + " where " + plugContrast.getPrimaryKey() + " >= "
                        + index[0] +" and " + plugContrast.getPrimaryKey() + " < " + index[1] + " order by "+ plugContrast.getPrimaryKey();
            ps = connection.prepareStatement(sql);
            logger.debug("------------sql------------{}--------------", sql);
            rs = ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData(); // 取得结果集的元元素
            int colCount = metaData.getColumnCount(); // 取得所有列的个数
            while (rs.next()){
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 1; i <= colCount; i++) {
                    Object value = rs.getObject(i);
                    map.put(metaData.getColumnName(i), value);
                }
                reslutMap.put(rs.getString(plugContrast.getPrimaryKey()), map);
            }
            return reslutMap;
        } catch (Exception e){
            e.printStackTrace();
            return reslutMap;
        } finally {
            try{
                if(rs != null)
                    rs.close();
                if(ps != null)
                    ps.close();
            } catch (SQLException e){
                e.printStackTrace();
            }
            dbConnectionManager.freeConnection(plugDatasource.getDsName(), connection);
        }
    }

    /**
     根据数据库别名、数据表名数据
     * @param plugDatasource 数据库名称
     * @param tableName 数据表名
     * @param offset 起始条数位置
     * @param limit 对比条数
     * @param column 查询列名称
     * @param plugContrast 任务配置对象
     * @return
     */
    public Map<String, Map<String, Object>> findHistoryRecord(PlugDatasource plugDatasource, String tableName, int offset, int limit, String column, PlugContrast plugContrast) throws Exception {
        Map<String,Map<String, Object>> reslutMap = new HashMap<>();
        Connection connection = dbConnectionManager.getConnection(DBConnectionPool.createJDBCInfoBean(plugDatasource));
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement("select " + column + " from " + tableName + " where "+plugContrast.getPrimaryKey()+" >= "
                    +offset+" order by "+ plugContrast.getPrimaryKey());
            logger.debug("------------sql------------{}--------------","select " + column + " from " + tableName + " where "+plugContrast.getPrimaryKey()+" > "
                    +offset+" order by "+ plugContrast.getPrimaryKey());
            rs = ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData(); // 取得结果集的元元素
            int colCount = metaData.getColumnCount(); // 取得所有列的个数
            while (rs.next()){
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 1; i <= colCount; i++) {
                    Object value = rs.getObject(i);
                    map.put(metaData.getColumnName(i), value);
                }
                reslutMap.put(rs.getString(plugContrast.getPrimaryKey()), map);
            }

        } catch (Exception e){
            e.printStackTrace();
            return reslutMap;
        } finally {
            try{
                if(rs != null)
                    rs.close();
                if(ps != null)
                    ps.close();
            } catch (SQLException e){
                e.printStackTrace();
            }
            dbConnectionManager.freeConnection(plugDatasource.getDsName(), connection);
        }
        return reslutMap;
    }

    public List<PlugRecord> findByOperationId(String crId,int offset,int limit) {
        return plugRecordMapper.findByOperationId(crId, offset, limit);
    }

    public int findByOperationIdTotal(String crId) {
        return plugRecordMapper.findByOperationIdTotal(crId);
    }

    /**
     *
     * @param plugDatasource 数据库名称
     * @param tableName 数据表名
     * @param primaryKey 主键开始位置
     * @param timeColumn 时间列
     * @param columnType 时间列类型
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param plugContrast 任务配置对象
     * @param isTime 是否是根据时间查询
     * @return
     */
    public Long findHistoryRecordTotal(PlugDatasource plugDatasource, String tableName, Long primaryKey,
                                       String timeColumn, String columnType, String startTime, String endTime,
                                       PlugContrast plugContrast, Boolean isTime) throws Exception {
        Connection connection = dbConnectionManager.getConnection(DBConnectionPool.createJDBCInfoBean(plugDatasource));
        PreparedStatement ps = null;
        ResultSet rs = null;
        Long result = 0L;
        try {
            String sql = "";
            if(isTime){
                sql = "select count(1) from " + tableName + " where " + timeColumn + " >= ? and " + timeColumn + " < ?  ";
                ps = connection.prepareStatement(sql);
                if (columnType.equals("bigint")) {
                    ps.setLong(1, DateConvert.convertLong(startTime));
                    ps.setLong(2, DateConvert.convertLong(endTime));
                } else if (columnType.equals("datetime")) {
                    ps.setObject(1, DateConvert.convertDate(startTime));
                    ps.setObject(2, DateConvert.convertDate(endTime));
                } else {
                    ps.setObject(1, startTime);
                    ps.setObject(2, endTime);
                }
            } else {
                sql = "select count(1) from " + tableName + " where " + plugContrast.getPrimaryKey() + " >= " + primaryKey;
                ps = connection.prepareStatement(sql);
            }
            rs = ps.executeQuery();
            while (rs.next()){
                result = rs.getLong(1);
            }
            logger.debug("-----------------------find sql-----{}------------------------------------------------",sql);
        } catch (Exception e){
            throw new Exception("查询对比数据异常，请检查对比表数据条件数据是否正确！");
        } finally {
            try {
                if(rs != null)
                    rs.close();
                if(ps != null)
                    ps.close();
            } catch (SQLException e){
                e.printStackTrace();
            }
            dbConnectionManager.freeConnection(plugDatasource.getDsName(), connection);
        }
        return result;
    }

    /**
     * 根据条件查询起始ID
     * @param plugDatasource 数据源对象
     * @param tableName 表名
     * @param column 查询列名
     * @param sort 排序方式
     * @param timeColumn 时间列
     * @param columnType 时间类型
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param plugContrast 任务配置对象
     * @return
     */
    public Long[] getMaxAndMinIndex(PlugDatasource plugDatasource, String tableName,
                             String column,String sort,String timeColumn,String columnType,
                             String startTime,String endTime,PlugContrast plugContrast) throws Exception {
        Connection connection = dbConnectionManager.getConnection(DBConnectionPool.createJDBCInfoBean(plugDatasource));
        PreparedStatement ps = null;
        ResultSet rs = null;
        Long[] index = new Long[2];
        Long maxIndex = 0L;
        Long minIndex = 0L;
        try {
            String sql = "select " + column + " from " + tableName + " where " + timeColumn + " >= ? and " + timeColumn + " < ? order by " + plugContrast.getPrimaryKey() + " " + sort;
            ps = connection.prepareStatement(sql);
            if (columnType.equals("bigint")) {
                ps.setLong(1, DateConvert.convertLong(startTime));
                ps.setLong(2, DateConvert.convertLong(endTime));
            } else if (columnType.equals("datetime")) {
                ps.setObject(1, DateConvert.convertDate(startTime));
                ps.setObject(2, DateConvert.convertDate(endTime));
            } else {
                ps.setObject(1, startTime);
                ps.setObject(2, endTime);
            }
            rs = ps.executeQuery();
            if(rs.first()){
                if(sort.equals("desc")){
                    maxIndex = Long.valueOf(rs.getString(plugContrast.getPrimaryKey()));
                } else {
                    minIndex = Long.valueOf(rs.getString(plugContrast.getPrimaryKey()));
                }
            }
            if(rs.last()){
                if(sort.equals("desc")){
                    minIndex = Long.valueOf(rs.getString(plugContrast.getPrimaryKey()));
                } else {
                    maxIndex = Long.valueOf(rs.getString(plugContrast.getPrimaryKey()));
                }
            }
            logger.debug("-----------------------find sql--------{},{}------{}",minIndex,maxIndex,sql);
            index[0] = minIndex;
            index[1] = maxIndex;
        } catch (Exception e){
            throw new Exception("查询对比数据异常，请检查对比表数据条件数据是否正确！");
        } finally {
            try {
                if(rs != null)
                    rs.close();
                if(ps != null)
                    ps.close();
            } catch (SQLException e){
                e.printStackTrace();
            }
            dbConnectionManager.freeConnection(plugDatasource.getDsName(), connection);
        }
        return index;
    }

    /**
     * 根据数据库别名、数据表名数据
     * @param plugDatasource 数据库名称
     * @param tableName 数据表名
     * @param primaryKey  主键开始位置
     * @param column 查询列名称
     * @param sort 排序方式
     * @param plugContrast 任务配置对象
     * @return
     */
    public Map<String, Map<String, Object>> findHistoryRecord(PlugDatasource plugDatasource, String tableName, Long primaryKey, String column, String sort, PlugContrast plugContrast) throws Exception {
        Map<String,Map<String, Object>> reslutMap = new HashMap<>();
        Connection connection = dbConnectionManager.getConnection(DBConnectionPool.createJDBCInfoBean(plugDatasource));
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement("select " + column + " from " + tableName + " where " + plugContrast.getPrimaryKey() + " >= "
                    + primaryKey + " order by " + plugContrast.getPrimaryKey());
            logger.debug("select " + column + " from " + tableName + " where "+plugContrast.getPrimaryKey()+" > "
                    + primaryKey+" order by "+ plugContrast.getPrimaryKey());
            rs = ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData(); // 取得结果集的元元素
            int colCount = metaData.getColumnCount(); // 取得所有列的个数
            while (rs.next()){
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 1; i <= colCount; i++) {
                    Object value = rs.getObject(i);
                    map.put(metaData.getColumnName(i), value);
                }
                reslutMap.put(rs.getString(plugContrast.getPrimaryKey()), map);
            }
        } catch (Exception e){
            e.printStackTrace();
            return reslutMap;
        } finally {
            try{
                if(rs != null)
                    rs.close();
                if(ps != null)
                    ps.close();
            } catch (SQLException e){
                e.printStackTrace();
            }
            dbConnectionManager.freeConnection(plugDatasource.getDsName(), connection);
        }
        return reslutMap;
    }

    /**
     * 根据数据库别名、数据表名数据
     * @param plugDatasource 数据库
     * @param tableName 数据表名
     * @param column 查询列名称
     * @return
     * @throws Exception
     */
    public Map<String, Map<String, Object>> findHistoryRecord(PlugDatasource plugDatasource, String tableName, String column,Long[] index,PlugContrast plugContrast) throws Exception {
        Map<String,Map<String, Object>> resultMap = new HashMap<>();
        Connection connection = dbConnectionManager.getConnection(DBConnectionPool.createJDBCInfoBean(plugDatasource));
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            String sql = "select " + column + " from " + tableName + " where " + plugContrast.getPrimaryKey() + " >= ? and " + plugContrast.getPrimaryKey() + " <= ? ";

            logger.debug("-----------------------find sql--------{}-------","select " + column + " from " + tableName + " where " + plugContrast.getPrimaryKey() + " >= ? and " + plugContrast.getPrimaryKey() + " < ?");
            ps = connection.prepareStatement(sql);
            ps.setObject(1, index[0]);
            ps.setObject(2, index[1]);
            rs = ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData(); // 取得结果集的元元素
            int colCount = metaData.getColumnCount(); // 取得所有列的个数
            while (rs.next()){
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 1; i <= colCount; i++) {
                    Object value = rs.getObject(i);
                    map.put(metaData.getColumnName(i), value);
                }
                resultMap.put(rs.getString(plugContrast.getPrimaryKey()), map);
            }
        } catch (Exception e){
            throw new Exception("查询对比数据异常，请检查对比表数据条件数据是否正确！");
        } finally {
            try {
                if(rs != null)
                rs.close();
                if(ps != null)
                ps.close();
            } catch (SQLException e){
                e.printStackTrace();
            }
            dbConnectionManager.freeConnection(plugDatasource.getDsName(), connection);
        }
        return resultMap;
    }
}
